package cn.hwua.dao.impl;

import cn.hwua.dao.ShopDao;
import cn.hwua.entity.Shop;
import cn.hwua.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class ShopDaoImpl implements ShopDao {
    Connection conn = DBUtil.getConnection();
    QueryRunner qr = new QueryRunner();

    @Override
    public int save(Shop obj) {
        String sql = "INSERT INTO  tb_shop(shop_id,shop_name,shop_img,shop_food,shop_businesshours,shop_rjxf,shop_zffs,shop_address,shop_tag,shop_addtime,shop_content) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
        Object[] paras = {obj.getShop_id(), obj.getShop_name(), obj.getShop_img(), obj.getShop_food(),
                obj.getShop_businesshours(), obj.getShop_rjxf(), obj.getShop_zffs(), obj.getShop_address(),
                obj.getShop_tag(), obj.getShop_addtime(), obj.getShop_content()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(Shop obj) {
        String sql = "UPDATE  tb_shop SET  shop_name=?,shop_img=?,shop_food=?,shop_businesshours=?,shop_rjxf=?,shop_zffs=?,shop_address=?,shop_tag=?,shop_addtime=?,shop_content=? WHERE shop_id=? ";
        Object[] paras = {obj.getShop_name(), obj.getShop_img(), obj.getShop_food(),
                obj.getShop_businesshours(), obj.getShop_rjxf(), obj.getShop_zffs(), obj.getShop_address(),
                obj.getShop_tag(), obj.getShop_addtime(), obj.getShop_content(), obj.getShop_id()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(Shop obj) {
        String sql = "DELETE FROM tb_shop WHERE shop_id  = ?  ";
        Object[] paras = {obj.getShop_id()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Shop findById(Object id) {
        String sql = "SELECT * FROM tb_shop where shop_id  = ?";
        Object[] paras = {id};
        try {
            Shop shop = qr.query(conn, sql, paras, new BeanHandler<Shop>(Shop.class));
            return shop;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Shop> findAll() {
        String sql = "SELECT * FROM tb_shop";
        Object[] paras = {};
        try {
            List<Shop> shopList = qr.query(conn, sql, paras, new BeanListHandler<Shop>(Shop.class));
            return shopList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Shop> findSearch(String keyword) {
        String sql = "SELECT * FROM tb_shop where shop_name like concat('%',concat(?,'%'))";
        Object[] paras = {keyword};
        try {
            List<Shop> shopList = qr.query(conn, sql, paras, new BeanListHandler<Shop>(Shop.class));
            return shopList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Shop> findPage(int page, int pagesize) {
        String sql = "SELECT * FROM tb_shop limit ?,?";
        Object[] paras = {(page - 1) * pagesize, pagesize};
        try {
            List<Shop> shopList = qr.query(conn, sql, paras, new BeanListHandler<Shop>(Shop.class));
            return shopList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long count() {
        String sql = "select count(*) from tb_shop";
        Object[] paras = {};
        try {
            long nums = qr.query(conn, sql, paras, new ScalarHandler<Long>());
            return nums;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Shop> find(Map<String, Object> map) {
        String sql = "SELECT * FROM tb_shop where 1=1";
        if (null != map.get("shop_name")) {
            sql += " and shop_name like '%"+map.get("shop_name").toString()+"%'";
        }
        if (null != map.get("shop_address")) {
            sql += " and shop_address = '"+map.get("shop_address").toString()+"'";
        }
        System.out.println("sql = " + sql);
        Object[] paras = {};
        try {
            List<Shop> shopList = qr.query(conn, sql, paras, new BeanListHandler<Shop>(Shop.class));
            return shopList;
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return null;
    }

    @Override
    public List<String> findAddress() {
        String sql = "SELECT distinct shop_address FROM tb_shop";
        Object[] paras = {};
        try {
            List<String> addressList = qr.query(conn, sql, paras, new ColumnListHandler<String>());
            return addressList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
